﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WMSSyncData.Model;
using WMSSyncData.Model.WMSModel;
using WMSSyncData.Utils;

namespace WMSSyncData.DAL.SqlDB
{
    public class Tddev107DAL : BaseSqlDBDAL<tddev107>
    {
        //***20190605 ADD by XCY 自动扣料
        public List<tddev107> GetNoMESData()
        {
            /* pdno 生产订单
             * sitm 物料
             * cwar 仓库
             * qune 发料数量
             * host 中间表状态
             * mess 信息
             * edte1 MES写入日期
             * wdte1 ERP读取日期
             * wdte2 ERP处理日期
             */
            var sql = @"SELECT NEWID() AS guid,
                      temp.RelatedBill AS pdno,
                            PartNumber AS sitm,
                      ISNULL(Ext1,' ') AS cwar,
                              temp.Qty AS qune,
                                     1 AS host,
                                 'WMS' AS flag,
                                   ' ' AS mess,
                             getdate() AS edte1,
                             getdate() AS wdte1,
                             getdate() AS wdte2,
                                     0 AS REFCNTD,
                                     0 AS REFCNTU
                      FROM ( SELECT
                        a.BillNo, a.RelatedBill,b.RowNo,   c.Storage_SN,c.PartNumber,SUM(c.RealQty) AS Qty,c.Version,
                        c.Location_SN,b.Ext1,b.Ext2 FROM dbo.wms_T_OutStockRM a
                        inner JOIN dbo.wms_T_OutStockRMRows b ON a.BillNo=b.BillNo
                        inner JOIN dbo.wms_T_OutStockRMRowDetails c ON b.BillNo=c.BillNo and b.RowNo=c.RowNo and b.OrderNo=c.OrderNo 
                        WHERE c.Status='8' and a.BillType='非拉动领料出库'  GROUP BY a.BillNo,a.RelatedBill,b.RowNo,c.Storage_SN,c.PartNumber,c.Location_SN,b.Ext1,b.Ext2,c.Version ) temp";
            var dt = SqlDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);
            return result;
        }

        public List<tddev107> GetSyncData()
        {
            var sql = @"SELECT NEWID() AS guid,temp.RelatedBill as pdno,temp.PartNumber as item,ISNULL(temp.Storage_SN,' ') as cwar,temp.Qty as qune,1 as host,' 'as mess,getdate() as edte1, getdate() as wdte1,getdate() as wdte2 ,0 as REFCNTD,0 as REFCNTU FROM ( SELECT a.RelatedBill,  a.Storage_SN,b.PartNumber,b.RealQty AS Qty FROM dbo.wms_T_InStockRM a
                        inner JOIN dbo.wms_T_InStockRMRows b ON a.BillNo=b.BillNo
                        WHERE a.Status='6' and BillType='非拉动退料入库'  ) temp";

            var dt = SqlDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);
            return result;
        }

        public bool UpdateUploadPOMOStatus()
        {
            var result = false;

            var sql = "UPDATE  wms_T_OutStockRMRowDetails SET Status='8' where Status='0' and billno in (select billno from wms_T_OutStockRM where billtype='非拉动领料出库') ";
            var updateRes = SqlDBHelper.ExcuteSQL(sql);
            if (updateRes > 0)
            {
                result = true;
            }
            return result;

        }


        public List<tddev107> GetPOMSyncData()
        {
            var sql = @"SELECT NEWID() AS guid,temp.RelatedBill as pdno,temp.PartNumber as item,ISNULL(temp.Storage_SN,' ') as cwar,temp.Qty as qune,1 as host,' 'as mess,getdate() as edte1, getdate() as wdte1,getdate() as wdte2 ,0 as REFCNTD,0 as REFCNTU  FROM ( SELECT
                    a.BillNo, a.RelatedBill,b.RowNo,   c.Storage_SN,c.PartNumber,SUM(c.RealQty) AS Qty,
                        c.Location_SN FROM dbo.wms_T_OutStockRM a
                        inner JOIN dbo.wms_T_OutStockRMRows b ON a.BillNo=b.BillNo
                        inner JOIN dbo.wms_T_OutStockRMRowDetails c ON b.BillNo=c.BillNo and b.RowNo=c.RowNo and b.OrderNo=c.OrderNo 
                        WHERE c.Status='8' and a.billtype='非拉动领料出库' GROUP BY a.BillNo,a.RelatedBill,b.RowNo,c.Storage_SN,c.PartNumber,c.Location_SN ) temp";

            var dt = SqlDBHelper.GetDataSet(sql).Tables[0];
            var result = MakeTablePackage(dt);
            return result;
        }

        public bool UpdateUploadPOMTStatus(List<WMS_T_OutStockRMRowDetails> list)
        {
            var result = false;
            //int dataOrderNo = Convert.ToInt32(DateTime.Now.Year.ToString().Substring(2, 2) + DateTime.Now.ToString("MMddHHmm"));
            var billNoList = list.Select(x => x.BillNo).Distinct();
            if (billNoList.Count() > 0)
            {
                string sqlwhere = "";
                foreach (string billno in billNoList)
                {
                    sqlwhere = sqlwhere + "'" + billno + "',";
                }
                sqlwhere = sqlwhere.TrimEnd(',');
                var sql = "UPDATE  wms_T_OutStockRMRowDetails SET Status='9'  where Status='8' and BillNo in (" + sqlwhere + "); ";
                var updateRes = SqlDBHelper.ExcuteSQL(sql);
                if (updateRes > 0)
                {
                    result = true;
                }
            }
            return result;

        }

        public bool UpdateUploadStatus(List<WMS_T_InstockRMRowDetails> list)
        {
            var result = false;
            var billNoList = list.Select(x => x.BillNo).Distinct();
            if (billNoList.Count() > 0)
            {
                var sqlWhere = "where BillNo in (";
                foreach (var item in billNoList)
                {
                    sqlWhere = sqlWhere + "'" + item + "',";
                }
                sqlWhere = sqlWhere.TrimEnd(',');
                sqlWhere = sqlWhere + ")";
                var sql = "UPDATE  wms_T_InStockRM SET Status='9'" + sqlWhere;
                var updateRes = SqlDBHelper.ExcuteSQL(sql);
                if (updateRes > 0)
                {
                    result = true;
                }
            }
            return result;

        }
    }
}
